package dm.repository;


import dm.ast.DmColumn;
import dm.ast.DmColumnType;
import dm.ast.DmTable;
import test.envset.RunEnv;
import test.utility.SqlRun;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * 元数据仓库
 * 生成sql需要使用得元数据库，例如表、视图等
 */
public class MetaRepo implements Runnable {
    private List<DmTable> usertables = null;
    private List<DmTable> systables = null;
    private boolean setTables = false;
    private List<DmTable> tables1 = new ArrayList<DmTable>();
    private List<DmTable> tables2 = new ArrayList<DmTable>();
    private int count = 1; //更新表的次数
    public ArrayList<ArrayList<IFunction>> dmIFunsLists = new ArrayList<>();

    public MetaRepo() {
        usertables = null;
    }

    public void init(String schemas, SqlRun sqlr) {
        if (count == 1) {
            System.out.println("initialize mata-repository......");
            setsqlrun(sqlr);
        }
        String[] tmps = schemas.split(",");

        //输出模式信息
        if (count == 1) {
            StringBuilder s1 = new StringBuilder();
            s1.append("schemas: ");
            if (schemas.equals("")) {
                s1.append("SYS...");
            } else {
                for (String str : tmps) {
                    s1.append(str + "...");
                }
            }
            System.out.println(s1.toString());
        }

        //获取用户定义的表
        for (int i = 0; i < tmps.length; i++) {
            if (!tmps[i].equals("SYS")) {
                initSchemaTableLow(tmps[i], sqlr);
            }
        }

        //获取用户定义的视图
        for (int i = 0; i < tmps.length; i++) {
            if (!tmps[i].equals("SYS")) {
                initSchemaViewleLow(tmps[i], sqlr);
            }
        }


        if (schemas.contains("SYS") || usertables == null || usertables.size() == 0) {
            initSysTables(sqlr);
            if (partTableNum() == 0 && count == 1) {
                System.err.println("get metadata table failed");
                System.exit(1);
            }
        }
        allTables();
        optionTab();

        if (count == 1) {
            System.out.println("initialize meta-repository finish! The number of partial tables:" + partTableNum());
        }
        count++;
    }

    public void initHints(SqlRun sqlr) {
        DmHintsInfo.initDmHintsInfo(sqlr);
    }

    public void initIFunctions(SqlRun sqlr) {
        DmIFunInfo.initDmIFunInfo(sqlr);
        dmIFunsLists = DmIFunInfo.dmIFuns;
    }

    public int partTableNum() {
        int count = 0;
        if (usertables != null) {
            count += usertables.size();
        }
        if (systables != null) {
            count += systables.size();
        }
        return count;
    }

    //todo
    //还需要处理分区表，目前将分区子表作为主表处理，有问题
    private void initSchemaTableLow(String schema, SqlRun sqlr) {
        //去除分区表得名字，仅保留主表名字
        String sql1 = "select name , ID,SUBTYPE$ from SYS.SYSOBJECTS where SUBTYPE$ = 'UTAB' and SCHID in "
                + "(select id from sys.sysobjects where name like '%s' and SUBTYPE$ is null)"
                + "AND ID not in(SELECT PART_TABLE_ID from SYSHPARTTABLEINFO) ORDER BY DBMS_RANDOM.VALUE fetch first 10 ROWS ONLY";
        //获取列名、类型、长度
        String sql2 = "select NAME,TYPE$ AS TY, LENGTH$ AS LENG from SYS.SYSCOLUMNS where id = %d";

        String tmpsql;
        ResultSet rs = null;

        if (usertables == null) {
            usertables = new ArrayList<DmTable>();
        }

        synchronized (usertables) {
            String schname = schema.trim();
            ArrayList<String> tabnames = new ArrayList<>();
            ArrayList<Integer> tabids = new ArrayList<>();
            ArrayList<String> type = new ArrayList<>();

            tmpsql = String.format(sql1, schname);
            rs = sqlr.sqlWithRsBegin(tmpsql);

            try {
                if (rs != null) {
                    while (rs.next()) {
                        tabnames.add(rs.getString(1)); // 获取单个模式下的所有表名
                        tabids.add(rs.getInt(2)); //获取单个模式下的所有对应表id
                        type.add(rs.getString(3)); //获取单个模式下的所有对应的类型
                    }
                }
            } catch (SQLException e) {
                System.err.println("initSchemaLow failed! schema:" + schname);
                e.printStackTrace();
            } finally {
                sqlr.sqlWithRsFinish();
            }

            for (int i = 0; i < tabnames.size(); i++) {
                tmpsql = String.format(sql2, tabids.get(i));
                DmTable tmptab = new DmTable(tabnames.get(i));
                tmptab.setTag(type.get(i));
                String.format(sql2, tabids.get(i));
                tmptab.schemaOf = schname;

                String sql3 = "SELECT PARTITION_NAME FROM SYSHPARTTABLEINFO where BASE_TABLE_ID in %d";
                String subsql = String.format(sql3, tabids.get(i));
                ResultSet subrs = sqlr.sqlWithRsBegin(subsql);

                try {
                    if (subrs != null) {
                        ArrayList<String> subTabs = new ArrayList<>();
                        while (subrs.next()) {
                            tmptab.addSubTab(subrs.getString(1));
                        }
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                rs = sqlr.sqlWithRsBegin(tmpsql);

                try {
                    if (rs != null) {
                        ArrayList<DmColumn> cols = new ArrayList<>();
                        while (rs.next()) {
                            cols.add(new DmColumn(rs.getString(1), new DmColumnType(rs.getString(2), rs.getInt(3)), tmptab));
                        }
                        tmptab.cols = cols;
                    }
                } catch (SQLException e) {
                    System.err.println("initSchemaLow failed! table" + tmptab.getFullName());
                    e.printStackTrace();
                } finally {
                    sqlr.sqlWithRsFinish();
                }

                if (tmptab.cols != null) {
                    usertables.add(tmptab);
                }
            }
        }
    }

    private void initSchemaViewleLow(String schema, SqlRun sqlr) {
        //去除分区表得名字，仅保留主表名字
        String sql1 = "select name , ID,SUBTYPE$ from SYS.SYSOBJECTS where SUBTYPE$ = 'VIEW' and SCHID in "
                + "(select id from sys.sysobjects where name like '%s' and SUBTYPE$ is null)"
                + "AND ID not in(SELECT PART_TABLE_ID from SYSHPARTTABLEINFO) ORDER BY DBMS_RANDOM.VALUE fetch first 10 ROWS ONLY";
        //获取列名、类型、长度
        String sql2 = "select NAME,TYPE$ AS TY, LENGTH$ AS LENG from SYS.SYSCOLUMNS where id = %d";

        String tmpsql;
        ResultSet rs = null;

        if (usertables == null) {
            usertables = new ArrayList<DmTable>();
        }

        synchronized (usertables) {
            String schname = schema.trim();
            ArrayList<String> tabnames = new ArrayList<>();
            ArrayList<Integer> tabids = new ArrayList<>();
            ArrayList<String> type = new ArrayList<>();

            tmpsql = String.format(sql1, schname);
            rs = sqlr.sqlWithRsBegin(tmpsql);

            try {
                if (rs != null) {
                    while (rs.next()) {
                        tabnames.add(rs.getString(1)); // 获取单个模式下的所有表名
                        tabids.add(rs.getInt(2)); //获取单个模式下的所有对应表id
                        type.add(rs.getString(3)); //获取单个模式下的所有对应的类型
                    }
                }
            } catch (SQLException e) {
                System.err.println("initSchemaLow failed! schema:" + schname);
                e.printStackTrace();
            } finally {
                sqlr.sqlWithRsFinish();
            }

            for (int i = 0; i < tabnames.size(); i++) {
                tmpsql = String.format(sql2, tabids.get(i));
                DmTable tmptab = new DmTable(tabnames.get(i));
                tmptab.setTag(type.get(i));
                String.format(sql2, tabids.get(i));
                tmptab.schemaOf = schname;

                String sql3 = "SELECT PARTITION_NAME FROM SYSHPARTTABLEINFO where BASE_TABLE_ID in %d";
                String subsql = String.format(sql3, tabids.get(i));
                ResultSet subrs = sqlr.sqlWithRsBegin(subsql);

                try {
                    if (subrs != null) {
                        ArrayList<String> subTabs = new ArrayList<>();
                        while (subrs.next()) {
                            tmptab.addSubTab(subrs.getString(1));
                        }
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                rs = sqlr.sqlWithRsBegin(tmpsql);

                try {
                    if (rs != null) {
                        ArrayList<DmColumn> cols = new ArrayList<>();
                        while (rs.next()) {
                            //有问题的列不参与填充
                            if (schema.equals("SYS")&&IsQuestionColOrView1(rs.getString(1))) {
                                continue;
                            }
                            cols.add(new DmColumn(rs.getString(1), new DmColumnType(rs.getString(2), rs.getInt(3)), tmptab));
                        }
                        tmptab.cols = cols;
                    }
                } catch (SQLException e) {
                    System.err.println("initSchemaLow failed! table" + tmptab.getFullName());
                    e.printStackTrace();
                } finally {
                    sqlr.sqlWithRsFinish();
                }

                if (tmptab.cols != null) {
                    usertables.add(tmptab);
                }
            }
        }
    }

    private void initSysTables(SqlRun sqlr) {
        //去除分区表得名字，仅保留主表名字
        String sql1 = "select name , ID,SUBTYPE$ from SYS.SYSOBJECTS where (SUBTYPE$ = 'UTAB' or SUBTYPE$ ='UTAB') and SCHID in "
                + "(select id from sys.sysobjects where name like 'SYS' and SUBTYPE$ is null)"
                + "AND ID not in(SELECT PART_TABLE_ID from SYSHPARTTABLEINFO) ORDER BY DBMS_RANDOM.VALUE fetch first 10 ROWS ONLY";
        //获取列名、类型、长度
        String sql2 = "select NAME,TYPE$ AS TY, LENGTH$ AS LENG from SYS.SYSCOLUMNS where id = %d";

        String tmpsql;
        ResultSet rs = null;

        if (systables == null) {
            systables = new ArrayList<DmTable>();
        }

        synchronized (systables) {

            ArrayList<String> tabnames = new ArrayList<>();
            ArrayList<Integer> tabids = new ArrayList<>();
            ArrayList<String> type = new ArrayList<>();

            tmpsql = String.format(sql1);
            rs = sqlr.sqlWithRsBegin(tmpsql);

            try {
                if (rs != null) {
                    while (rs.next()) {
                        //有问题的列不参与填充
                        if (IsQuestionColOrView1(rs.getString(1))) {
                            continue;
                        }
                        tabnames.add(rs.getString(1)); // 获取单个模式下的所有表名
                        tabids.add(rs.getInt(2)); //获取单个模式下的所有对应表id
                        type.add(rs.getString(3)); //获取单个模式下的所有对应的类型
                    }
                }
            } catch (SQLException e) {
                System.err.println("initSchemaLow failed! schema:" + "SYS");
                e.printStackTrace();
            } finally {
                sqlr.sqlWithRsFinish();
            }

            for (int i = 0; i < tabnames.size(); i++) {
                tmpsql = String.format(sql2, tabids.get(i));
                DmTable tmptab = new DmTable(tabnames.get(i));
                tmptab.setTag(type.get(i));
                String.format(sql2, tabids.get(i));
                tmptab.schemaOf = "SYS";

                String sql3 = "SELECT PARTITION_NAME FROM SYSHPARTTABLEINFO where BASE_TABLE_ID in %d";
                String subsql = String.format(sql3, tabids.get(i));
                ResultSet subrs = sqlr.sqlWithRsBegin(subsql);

                try {
                    if (subrs != null) {
                        ArrayList<String> subTabs = new ArrayList<>();
                        while (subrs.next()) {
                            tmptab.addSubTab(subrs.getString(1));
                        }
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                rs = sqlr.sqlWithRsBegin(tmpsql);

                try {
                    if (rs != null) {
                        ArrayList<DmColumn> cols = new ArrayList<>();
                        while (rs.next()) {
                            //有问题的列不参与填充
                            if (IsQuestionColOrView1(rs.getString(1))) {
                                continue;
                            }
                            cols.add(new DmColumn(rs.getString(1), new DmColumnType(rs.getString(2), rs.getInt(3)), tmptab));
                        }
                        tmptab.cols = cols;
                    }
                } catch (SQLException e) {
                    System.err.println("initSchemaLow failed! table" + tmptab.getFullName());
                    e.printStackTrace();
                } finally {
                    sqlr.sqlWithRsFinish();
                }

                if (tmptab.cols != null) {
                    usertables.add(tmptab);
                }
            }
        }
    }

    public boolean IsQuestionColOrView1(String name){
        String[] patternString={
                "FUNCTION","INTERVAL","SECTION","HOST","USERNAME","MVIEW_TYPE","REWRITE_ENABLED","REFRESH_METHOD","REFRESH_MODE","NAME","SQL_TEXT","DBA_CONS_COLUMNS"
                ,"SPACE_USED","DESCRIPTION","USER_MVIEWS","ALL_DB_LINKS","END_ROWID","VSYSAUDITSQLSEQ","VSYSMACCOMPS","VSYSMACPLYS","VSYSMACUSRPLY","VSYSMACLABELS","##HISTOGRAMS_TABLE"
                ,"SYSSSO","AQ$_QUEUES","VSYSAUDITRULES","VSYAUDIT","USER_TRIGGER_COLS","VSYSPKGPROCPARAMS","VSYSOBJVIEWS","USER_TABLES"        };
        Pattern[] patterns=new Pattern[patternString.length];
        int count=0;
        for (int i=0;i<patterns.length;i++){
            patterns[i]=Pattern.compile(patternString[i]);

        }

        for (Pattern pattern:patterns){
            Matcher matcher=pattern.matcher(name);

            if (matcher.find()) {
                count++;
                break;
            }
            if (name.equals("AQ$_QUEUES")) {
                count++;
                break;
            }
        }

        if (count>0) {
            return true;
        }else {
            return false;
        }
    }

    private SqlRun sqlr;

    private boolean termflag =false;

    public void setsqlrun(SqlRun sqlr){
        this.sqlr=sqlr;
    }
    public void termFlag(){
        termflag=true;
    }

    @Override
    public void run(){
        try {
            while (!termflag){
                Thread.sleep(1000*120);
                if (!termflag) {
                    systables=null;
                    usertables=null;
                    init(RunEnv.META_SCHEMAS,sqlr);
                    if (setTables==true) {
                        tables2=new ArrayList<>();
                    }else{
                        tables1=new ArrayList<>();
                    }
                }
            }
            Thread.interrupted();
        }catch (InterruptedException e){

        }

    }

    private List<DmTable> allTables=null;

    public void allTables(){
        allTables=new ArrayList<>();
        if (usertables!=null&&usertables.size()!=0) {
            allTables.addAll(usertables);
        }
        if (systables!=null&&systables.size()!=0) {
            allTables.addAll(systables);
        }
    }
    public void optionTab(){
        if (setTables) {
            tables2.addAll(allTables);
            setTables=false;
        }else{
            tables1.addAll(allTables);
            setTables=true;
        }
    }

    public List<DmTable> getTables(){
        if (setTables) {
            return tables1;
        }else {
            return tables2;
        }

    }


}
